package com.dc.dao;

import com.dc.dao.vo.ShipAndRepairVO;
import com.dc.entity.ShipRepairEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.web.bind.annotation.PathVariable;

import java.util.Date;
import java.util.List;
import java.util.Map;

public interface ShipAndRepairDao extends JpaRepository<ShipRepairEntity, Long>, JpaSpecificationExecutor<ShipRepairEntity> {

    // enterTimeBegin  enterTimeEnd  id
    @Query(nativeQuery = true,
            value = "SELECT " +
                    " tsr.id as id, " +
                    " ts.name as name, " +
                    " ts.id as shipId," +
                    " ts.type as type," +
                    " tsr.code as code, " +
                    " tsr.manager as manager, " +
                    " tsr.manager_tel as managerTel, " +
                    " tsr.work_flows as workFlows, " +
                    " tsr.enter_time as enterTime, " +
                    " tsr.start_time as startTime, " +
                    " tsr.end_time as endTime, " +
                    " tsr.complete as complete, " +
                    " tsr.out_time as outTime " +
                    " FROM  t_ship_repair tsr " +
                    " JOIN t_ship ts ON tsr.ship_id = ts.id " +
                    " where enter_time >= :enterTimeBegin and enter_time <= :enterTimeEnd " +
                    " AND ts.id = :id",
            countQuery = "SELECT count(1) FROM  t_ship_repair tsr JOIN t_ship ts ON tsr.ship_id = ts.id   where enter_time >= :enterTimeBegin and enter_time <= :enterTimeEnd  AND ts.id = :id")
    public Page<ShipAndRepairVO> findShipAndRepairVOByEnterTimeBeginAndEnterTimeEndAndId(
            @Param("enterTimeBegin") Date enterTimeBegin,
            @Param("enterTimeEnd") Date enterTimeEnd,
            @Param("id") Long id,
            Pageable pageable);


    // id
    @Query(nativeQuery = true,
            value = "SELECT " +
                    " tsr.id as id, " +
                    " ts.name as name, " +
                    " ts.id as shipId," +
                    " ts.type as type," +
                    " tsr.code as code, " +
                    " tsr.manager as manager, " +
                    " tsr.manager_tel as managerTel, " +
                    " tsr.work_flows as workFlows, " +
                    " tsr.enter_time as enterTime, " +
                    " tsr.start_time as startTime, " +
                    " tsr.end_time as endTime, " +
                    " tsr.complete as complete, " +
                    " tsr.out_time as outTime " +
                    " FROM  t_ship_repair tsr " +
                    " JOIN t_ship ts ON tsr.ship_id = ts.id " +
                    " where  ts.id = :id",
            countQuery = "SELECT count(1) FROM  t_ship_repair tsr JOIN t_ship ts ON tsr.ship_id = ts.id  where  ts.id = :id")
    public Page<ShipAndRepairVO> findShipAndRepairVOByShipId(@Param("id") Long id, Pageable pageable);


    // enterTimeBegin
    @Query(nativeQuery = true,
            value = "SELECT " +
                    " tsr.id as id, " +
                    " ts.name as name, " +
                    " ts.id as shipId," +
                    " ts.type as type," +
                    " tsr.code as code, " +
                    " tsr.manager as manager, " +
                    " tsr.manager_tel as managerTel, " +
                    " tsr.work_flows as workFlows, " +
                    " tsr.enter_time as enterTime, " +
                    " tsr.start_time as startTime, " +
                    " tsr.end_time as endTime, " +
                    " tsr.complete as complete, " +
                    " tsr.out_time as outTime " +
                    " FROM  t_ship_repair tsr " +
                    " JOIN t_ship ts ON tsr.ship_id = ts.id " +
                    " where enter_time >= :enterTimeBegin",
            countQuery = "SELECT count(1) FROM  t_ship_repair tsr JOIN t_ship ts ON tsr.ship_id = ts.id  where enter_time >= :enterTimeBegin")
    public Page<ShipAndRepairVO> findShipAndRepairVOByEnterTimeBegin(@Param("enterTimeBegin") Date enterTimeBegin, Pageable pageable);


    // enterTimeEnd
    @Query(nativeQuery = true,
            value = "SELECT " +
                    " tsr.id as id, " +
                    " ts.name as name, " +
                    " ts.id as shipId," +
                    " ts.type as type," +
                    " tsr.code as code, " +
                    " tsr.manager as manager, " +
                    " tsr.manager_tel as managerTel, " +
                    " tsr.work_flows as workFlows, " +
                    " tsr.enter_time as enterTime, " +
                    " tsr.start_time as startTime, " +
                    " tsr.end_time as endTime, " +
                    " tsr.complete as complete, " +
                    " tsr.out_time as outTime " +
                    " FROM  t_ship_repair tsr " +
                    " JOIN t_ship ts ON tsr.ship_id = ts.id " +
                    " where enter_time <= :enterTimeEnd",
            countQuery = "SELECT count(1) FROM  t_ship_repair tsr JOIN t_ship ts ON tsr.ship_id = ts.id  where enter_time <= :enterTimeEnd")
    public Page<ShipAndRepairVO> findShipAndRepairVOByEnterTimeEnd(@Param("enterTimeEnd") Date enterTimeEnd, Pageable pageable);

    // enterTimeBegin enterTimeEnd
    @Query(nativeQuery = true,
            value = "SELECT " +
                    " tsr.id as id, " +
                    " ts.name as name, " +
                    " ts.id as shipId," +
                    " ts.type as type," +
                    " tsr.code as code, " +
                    " tsr.manager as manager, " +
                    " tsr.manager_tel as managerTel, " +
                    " tsr.work_flows as workFlows, " +
                    " tsr.enter_time as enterTime, " +
                    " tsr.start_time as startTime, " +
                    " tsr.end_time as endTime, " +
                    " tsr.complete as complete, " +
                    " tsr.out_time as outTime " +
                    " FROM  t_ship_repair tsr " +
                    " JOIN t_ship ts ON tsr.ship_id = ts.id " +
                    " where enter_time >= :enterTimeBegin and enter_time <= :enterTimeEnd",
            countQuery = "SELECT count(1) FROM  t_ship_repair tsr JOIN t_ship ts ON tsr.ship_id = ts.id  where enter_time >= :enterTimeBegin and enter_time <= :enterTimeEnd")
    public Page<ShipAndRepairVO> findShipAndRepairVOByEnterTimeBeginAndEnterTimeEnd(
            @Param("enterTimeBegin") Date enterTimeBegin,
            @Param("enterTimeEnd") Date enterTimeEnd,
            Pageable pageable);

    @Query(nativeQuery = true,
            value = "SELECT " +
                    " tsr.id as id, " +
                    " ts.name as name, " +
                    " ts.id as shipId," +
                    " ts.type as type," +
                    " tsr.code as code, " +
                    " tsr.manager as manager, " +
                    " tsr.manager_tel as managerTel, " +
                    " tsr.work_flows as workFlows, " +
                    " tsr.enter_time as enterTime, " +
                    " tsr.start_time as startTime, " +
                    " tsr.end_time as endTime, " +
                    " tsr.complete as complete, " +
                    " tsr.out_time as outTime " +
                    " FROM  t_ship_repair tsr " +
                    " JOIN t_ship ts ON tsr.ship_id = ts.id " +
                    " where enter_time >= :enterTimeBegin and ts.id = :id",
            countQuery = "SELECT count(1) FROM  t_ship_repair tsr JOIN t_ship ts ON tsr.ship_id = ts.id  where enter_time >= :enterTimeBegin and ts.id = :id")
    public Page<ShipAndRepairVO> findShipAndRepairVOByEnterTimeBeginAndId(
            @Param("enterTimeBegin") Date enterTimeBegin,
            @Param("id") Long id,
            Pageable pageable);

    @Query(nativeQuery = true,
            value = "SELECT " +
                    " tsr.id as id, " +
                    " ts.name as name, " +
                    " ts.id as shipId," +
                    " ts.type as type," +
                    " tsr.code as code, " +
                    " tsr.manager as manager, " +
                    " tsr.manager_tel as managerTel, " +
                    " tsr.work_flows as workFlows, " +
                    " tsr.enter_time as enterTime, " +
                    " tsr.start_time as startTime, " +
                    " tsr.end_time as endTime, " +
                    " tsr.complete as complete, " +
                    " tsr.out_time as outTime " +
                    " FROM  t_ship_repair tsr " +
                    " JOIN t_ship ts ON tsr.ship_id = ts.id " +
                    " where enter_time <= :enterTimeEnd and ts.id = :id",
            countQuery = "SELECT count(1) FROM  t_ship_repair tsr JOIN t_ship ts ON tsr.ship_id = ts.id  where enter_time <= :enterTimeEnd and ts.id = :id")
    public Page<ShipAndRepairVO> findShipAndRepairVOByEnterTimeEndAndId(
            @Param("enterTimeEnd") Date enterTimeEnd,
            @Param("id") Long id,
            Pageable pageable);


    @Query(nativeQuery = true,
            value = "SELECT " +
                    " tsr.id as id, " +
                    " ts.name as name, " +
                    " ts.id as shipId," +
                    " ts.type as type," +
                    " tsr.code as code, " +
                    " tsr.manager as manager, " +
                    " tsr.manager_tel as managerTel, " +
                    " tsr.work_flows as workFlows, " +
                    " tsr.enter_time as enterTime, " +
                    " tsr.start_time as startTime, " +
                    " tsr.end_time as endTime, " +
                    " tsr.complete as complete, " +
                    " tsr.out_time as outTime " +
                    " FROM  t_ship_repair tsr " +
                    " JOIN t_ship ts ON tsr.ship_id = ts.id",
            countQuery = "SELECT count(1) FROM  t_ship_repair tsr JOIN t_ship ts ON tsr.ship_id = ts.id")
    public Page<ShipAndRepairVO> findShipAndRepairVO(Pageable pageable);



}
